﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WaveMaster.Holidays.Club.Gateway;
using WaveMaster.Holidays.Club.Domain;
using System.Collections;
using MySql.Data.MySqlClient;
using WaveMaster.Holidays.Club.SQL;
using System.Data;

namespace WaveMaster.Holidays.Club.Service
{
    public class TicketDeductionService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public TicketDeductionService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
        }
        public TicketDeductionService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public IList LoadAllTicketDeduction()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.GetAllTicketDeduction(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    TicketDeduction user = new TicketDeduction(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList LoadAllTicketDeduction(long travelAgentID)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.GetAllTicketDeductionByAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    TicketDeduction user = new TicketDeduction(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public TicketDeduction GetTicketDeductionByID(long id)
        {
            TicketDeduction result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.GetTicketDeductionByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_deductID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new TicketDeduction(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearcTicketDeductionByCodeName(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.SearchTicketDeductionByCodeName(p), m_dataBaseConectivity.InterfaceSqlConnection);
                //cmd.Parameters.Add("code", MySqlDbType.VarChar).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new TicketDeduction(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearcTicketDeductionByCodeNameTravelAgent(string p, long travelAgentID)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.SearchTicketDeductionByCodeNameTravelAgent(p), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgentID;
                //cmd.Parameters.Add("code", MySqlDbType.VarChar).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new TicketDeduction(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void DeleteTicketDeduction(TicketDeduction tp)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.DeleteTicketDeductionByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_deductID", MySqlDbType.Int32).Value = tp.m_deductID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();

                //UPDATE MINUS NUMBER TicketDeduction
                //cmd.Parameters.Clear();
                //cmd.CommandText = AutoNumberSQL.UpdateMinusAutoNumber();
                //cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = user.TravelAgent.TravelAgentID;
                //cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketDeduction";
                //cmd.ExecuteNonQuery();

                //UPDATE STOCK BOOK
                cmd.Parameters.Clear();
                if (tp.m_ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.UpdateAddOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.UpdateAddTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("addition", MySqlDbType.Decimal).Value = tp.m_quantity;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateTicketDeduction(TicketDeduction tp)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.GetTicketDeductionByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_deductID", MySqlDbType.VarChar).Value = tp.m_deductID;
                MySqlDataReader rd = cmd.ExecuteReader();
                TicketDeduction lasttp = null;
                while (rd.Read())
                {
                    lasttp = new TicketDeduction(rd);
                }
                rd.Close();
                //UPdate bookstock substract stock 
                cmd.Parameters.Clear();
                if (lasttp.m_ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.UpdateAddOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.UpdateAddTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = lasttp.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("addition", MySqlDbType.Decimal).Value = lasttp.m_quantity;
                cmd.ExecuteNonQuery();
                //UPdate bookstock add new stock 
                cmd.Parameters.Clear();
                if (tp.m_ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.UpdateSubstractOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.UpdateSubstractTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("substract", MySqlDbType.Decimal).Value = tp.m_quantity;
                cmd.ExecuteNonQuery();

                //update deduction
                cmd.Parameters.Clear();
                cmd.CommandText = TicketDeductionSQL.UpdateTicketDeduction();
                cmd.Parameters.Add("m_code", MySqlDbType.VarChar).Value = tp.m_code;
                cmd.Parameters.Add("m_transactionDate", MySqlDbType.Datetime).Value = tp.m_transactionDate;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_quantity", MySqlDbType.Decimal).Value = tp.m_quantity;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = tp.m_ticketType;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = tp.m_remark;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = tp.m_modfctnBy;
                cmd.Parameters.Add("m_deductID", MySqlDbType.Int32).Value = tp.m_deductID;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            } 
        }

        public void InserTicketDeduction(TicketDeduction tp)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                //GEt AUTO NUMBER
                  bool auto = tp.m_code.ToLower() == "autonumber";
                MySqlCommand cmd = new MySqlCommand(); 
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                if (auto)
                {
                    cmd = new MySqlCommand(AutoNumberSQL.GetLatestNumberByAgentAndType(), m_dataBaseConectivity.InterfaceSqlConnection);
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketDeduction";
                    int nextnumber = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                    string nextnumbers = nextnumber.ToString().PadLeft(6, '0');
                    //tp.m_code = "WMM-" + tp.m_travelAgent.Code + "-D" + nextnumbers;
                    tp.m_code = tp.m_travelAgent.Code + "-D" + nextnumbers;
                }
                //INSERT TA
                cmd.Parameters.Clear();
                cmd.CommandText = TicketDeductionSQL.InsertTicketDeduction();
                cmd.Parameters.Add("m_code", MySqlDbType.VarChar).Value = tp.m_code;
                cmd.Parameters.Add("m_transactionDate", MySqlDbType.Datetime).Value = tp.m_transactionDate;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_quantity", MySqlDbType.Decimal).Value = tp.m_quantity;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = tp.m_ticketType;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = tp.m_remark;
                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = tp.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = tp.m_cretnBy;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();

                //UPDATE NEXT NUMBER TicketDeduction
                if (auto)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = AutoNumberSQL.UpdateLatestAutoNumber();
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketDeduction";
                    cmd.ExecuteNonQuery();
                }
                //UPDATE STOCK BOOK
                cmd.Parameters.Clear();
                if(tp.m_ticketType== TicketType.OneWay)
                    cmd.CommandText = BookingSQL.UpdateSubstractOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.UpdateSubstractTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = tp.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("substract", MySqlDbType.Decimal).Value = tp.m_quantity;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public int SearchTicketDeductionByAgent(long agent)
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.GetCountTicketDeductionByAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = agent;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                result = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public DataSet ReportTicketDeductionByDateAndTravelAgent(DateTime fromDate, DateTime toDate, long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.ReportTicketDeductionByDateAndTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (id > 0)
                {
                    cmd.CommandText += " and p.m_travelAgentId=?m_travelAgentId";
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = id;
                }
                cmd.Parameters.Add("fromdate", MySqlDbType.Datetime).Value = fromDate;
                cmd.Parameters.Add("todate", MySqlDbType.Datetime).Value = toDate;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public DataSet ReportTicketDeductionByID(long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TicketDeductionSQL.GetTicketDeductionByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_deductID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
    }
}
